﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Collections;

namespace SWEN3Application.Classes
{
    class GeneralModuleDBManager
    {
        public UserAccount login(string username, string password)
        {
            SqlCeConnection conn = null;
            UserAccount useracc = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAccount WHERE Username=@username AND Password=@password";
                comm.Parameters.AddWithValue("@username", username);
                comm.Parameters.AddWithValue("@password", password);
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useracc = new UserAccount();
                    useracc.Username = (string)dr["Username"];
                    useracc.Password = (string)dr["Password"];
                    useracc.UserType = (int)dr["UserLevel"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return useracc;
        }

        public int createUserAccount(UserAccount ua)
        {
            SqlCeConnection conn = null;
            int rowinserted = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO UserAccount(Username, Password, UserLevel, EmailAddress) VALUES (@username, @password, @userlevel, @emailaddress)";
                comm.Parameters.AddWithValue("@username", ua.Username);
                comm.Parameters.AddWithValue("@password", ua.Password);
                comm.Parameters.AddWithValue("@userlevel", ua.UserType);
                comm.Parameters.AddWithValue("@emailaddress", ua.EmailAddress);
                rowinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowinserted;
        }

        public int DeleteUserAccount(string username)
        {
            SqlCeConnection conn = null;
            int rowsdeleted = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from UserAccount where Username=@username";

                comm.Parameters.AddWithValue("@username", username);
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsdeleted;
        }

        public ArrayList ViewUserAccount()
        {
            ArrayList result = new ArrayList();
            SqlCeConnection conn = null;
            UserAccount useracc = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Username FROM UserAccount";
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useracc = new UserAccount();
                    useracc.Username = (string)dr["Username"];
                    result.Add(useracc);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public UserAccount ShowUserAccount(string username)
        {
            SqlCeConnection conn = null;
            UserAccount useracc = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAccount WHERE Username=@username";
                comm.Parameters.AddWithValue("@username", username);
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useracc = new UserAccount();
                    useracc.Username = (string)dr["Username"];
                    useracc.Password = (string)dr["Password"];
                    useracc.UserType = (int)dr["UserLevel"];
                    useracc.EmailAddress = (string)dr["EmailAddress"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return useracc;
        }
    }
}
